/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor+
 */

package DAL;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.concurrent.Callable;

/**
 *
 * @author Nguyen Phi Hung
 */
public class ConnectUnit {
    /***/
    private SQLConnect connect;

    /**
     * Help to Select Database
     * SELECT * FROM TableName WHERE condition ORDER BY OrderBy;
     * @param TableName
     * @param Condition
     * @param OrderBy
     * @return
     * @throws Exception 
     */
    public CallableStatement GetCallableStatement(String sql) throws Exception
    {
        return this.connect.getConnect().prepareCall(sql);
    }
    public ResultSet Select(String TableName, String Condition,String OrderBy) throws Exception{
        //Create string Select.
        StringBuilder query = new StringBuilder( "SELECT * FROM " + TableName);
        // put SQLstring into query
        this.AddCondition(query, Condition);
        // put Order into query
        this.AddOrderBy(query, OrderBy);
        // insert ";" in the last of SQLstring
        query.append(";");
        
        return this.connect.executeQuery(query.toString());
    }

    /**
     * Function overload Select reduce OrderBy parameter
     * @param TableName
     * @param Condition
     * @return
     * @throws Exception 
     */
    public ResultSet Select(String TableName, String Condition) throws Exception{
        return this.Select(TableName, Condition,null);
    }
    
    /**
     * Function overload Select reduce Condition parameter
     * @param TableName
     * @return
     * @throws Exception 
     */
     public ResultSet Select(String TableName) throws Exception{
        return this.Select(TableName, null);
    }

     /**
      * Function add condition into query
      * @param query
      * @param Condition 
      */
     private void AddCondition(StringBuilder query, String Condition){
         // check condition
         if(Condition!=null)
            query.append(" WHERE ").append(Condition);
    }

    /**
     * Function add OrderBy into query
     * @param query
     * @param OrderBy 
     */
    private void AddOrderBy(StringBuilder query,String OrderBy){
        // Check OrderBy
        if(OrderBy!=null)
            query.append(" ORDER BY ").append(OrderBy);
    }

    /**
     * Function help to Insert into Database
     * INSERT INTO TableName (columnName...) VALUES (column Values...);
     * @param TableName
     * @param ColumnValues
     * @return
     * @throws Exception 
     */
    public boolean Insert(String TableName, HashMap<String,Object> ColumnValues, String[] array) throws Exception{
        StringBuilder query = new StringBuilder( "INSERT INTO "+ TableName);
        StringBuilder values = new StringBuilder(); 
        query.append("(");
        
        for(int i  = 0; i< array.length; i++){
            query.append(array[i]).append(",");    
            values = values.append("'").append(ColumnValues.get(array[i])).append("' ,");
        }
        
        //Cut and drop "," at the end of string
        query = query.delete(query.length()-1, query.length());
        values = values.delete(values.length()-1, values.length());
        
        //Insert values of row into query
        query.append(") VALUES(").append(values.toString()).append(")");
        // executeUpdate
        System.out.println(query);
        return this.connect.executeUpdate(query.toString()) > 0;              
    }

    /**
     * Function help update database
     * UPDATE TableName SET ColumnName = ColumnValue WHERE Condition
     * @param TableName
     * @param ColumnValues
     * @param Condition
     * @return
     * @throws Exception 
     */
    public boolean Update(String TableName, HashMap<String,Object> ColumnValues, String Condition) throws Exception {
        //Creater StringBuilder to creater string Select
        StringBuilder query = new StringBuilder("UPDATE " + TableName + " SET ");

        // Browse and put the column names and information of value to the query column
        for(String key : ColumnValues.keySet()){
            StringBuilder append = query.append(key).append(" = '").append(ColumnValues.get(key).toString()).append("',");
        }
        // Cut and drop ";" at the end of string
        query = query.delete(query.length()-1, query.length());
        // Add condition into query
        this.AddCondition(query, Condition);
        // insert char ';' at the end of the command line to commands
        query.append(";");
        // executeUpdate and return
        return this.connect.executeUpdate(query.toString())> 0 ;
    }

    /**
     * Function help DELETE in database
     * DELETE FROM TableName WHERE Condition
     * @param TableName
     * @param Condition
     * @return
     * @throws Exception 
     */
    public boolean Delete(String TableName,String Condition) throws Exception{
        // Creater StringBuilder to creater string Select
        StringBuilder query = new StringBuilder( "DELETE FROM " + TableName);
        // Add condition into query
        this.AddCondition(query, Condition);
        // insert char ';' at the end of the command line to commands
        query.append(";");
        // executeUpdate and return
        return this.connect.executeUpdate(query.toString()) > 0;
    }

    /**
     * Count row of Result select from database
     * @param result
     * @return
     * @throws SQLException 
     */
    public static int getColumnCount(ResultSet result) throws SQLException{
        //get the number of row in ResultSet
        return result.getMetaData().getColumnCount();
    }

    /**
     * Function get list of name of column in Result select from database
     * @param result
     * @return
     * @throws SQLException 
     */
    public static String[]getColumnName(ResultSet result) throws SQLException{
        // get ResultSetMetaDate from Result
        ResultSetMetaData rsMetaData = (ResultSetMetaData)result.getMetaData();
        // Get the number of row of Result.
        int ColumnCount = rsMetaData.getColumnCount();
        // 
        String[] list = new String[ColumnCount];
        // browse column
        for(int i=0;i<ColumnCount;i++)
            // get name of row put into list
            list[i]=rsMetaData.getColumnName(i);
        // return
        return list;
    }

    /**
     * Close ConnectUnit
     * @throws SQLException
     * @throws Exception 
     */
    public void Close() throws SQLException, Exception{
        this.connect.disconnect();}

    /**
     * Operation of ConnectUnit
     * @param user
     * @param pass
     * @param db 
     */
    public ConnectUnit(String host,String user,String pass,String db) {
            this.connect = new SQLConnect(host,user,pass,db);
    }
}
